Skip Headers

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# About User-Managed Media Recovery

To perform recovery, Oracle recommends that you use the `RECOVER` SQL statement in SQL*Plus. You can also use the SQL statement `ALTER` `DATABASE` `RECOVER`, but the RECOVER statement is simpler in most cases.

## Preconditions of Performing User-Managed Recovery

To start any type of media recovery, you must adhere to the following restrictions:

- You must have administrator privileges.

- All recovery sessions must be compatible.

- One session cannot start complete media recovery while another performs incomplete media recovery.

- You cannot start media recovery if you are connected to the database through a shared server process.

## Applying Logs Automatically with the RECOVER Command

Oracle recommends that you use the SQL*Plus `RECOVER` command rather than the `ALTER` `DATABASE` `RECOVER` statement to perform media recovery. In almost all cases, the SQL*Plus method is easier.

When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.

When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:

- Issuing `SET` `AUTORECOVERY` `ON` before issuing the `RECOVER` command

- Specifying the `AUTOMATIC` keyword as an option of the `RECOVER` command

In either case, no interaction is required when you issue the `RECOVER` command if the necessary files are in the correct locations with the correct names. The filenames used when you use automatic recovery are derived from the concatenated values of `LOG_ARCHIVE_FORMAT` with `LOG_ARCHIVE_DEST_``n`, where `n` is the highest value among all enabled, local destinations.

For example, assume the following initialization parameter settings are in effect in the database instance:

    LOG_ARCHIVE_DEST_1 = "LOCATION=/arc_dest/loc1/"
    LOG_ARCHIVE_DEST_2 = "LOCATION=/arc_dest/loc2/"
    LOG_ARCHIVE_DEST_STATE_1 = DEFER
    LOG_ARCHIVE_DEST_STATE_2 = ENABLE
    LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc

In this case, SQL*Plus automatically suggests the filename `/arc_dest/loc2/arch_%t_%s_%r.arc` (where `%t` is the thread, `%s` is the sequence and `%r` is the resetlogs ID).

If you run `SET` `AUTORECOVERY` `OFF`, which is the default option, then you must enter the filenames manually, or accept the suggested default filename by pressing the Enter key.

### Automating Recovery with SET AUTORECOVERY

Run the `SET` `AUTORECOVERY` `ON` command to enable on automatic recovery.

To automate the recovery using SET AUTORECOVERY:

1. Restore a backup of the offline datafiles. This example restores an inconsistent backup of all datafiles with an operating system utility:

       % cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/

2. Ensure the database is mounted. For example, if the database is shut down, run:

       STARTUP MOUNT

3. Enable automatic recovery. For example, in SQL*Plus run:

       SET AUTORECOVERY ON

4. Recover the desired datafiles. This example recovers the whole database:

       RECOVER DATABASE

   The database automatically suggests and applies the necessary archived logs.

5. Open the database. For example:

       ALTER DATABASE OPEN;

   Note: After issuing the SQL*Plus `RECOVER` command, you can view all files that have been considered for recovery in the `V$RECOVERY_FILE_STATUS` view. You can access status information for each file in the `V$RECOVERY_STATUS` view. These views are not accessible after you terminate the recovery session.

### Automating Recovery with the AUTOMATIC Option of the RECOVER Command

Besides using `SET` `AUTORECOVERY` to turn on automatic recovery, you can also simply specify the `AUTOMATIC` keyword in the `RECOVER` command.

To automate the recovery with the RECOVER AUTOMATIC command:

1. Restore a backup of the offline datafiles. This example restores a backup of all datafiles:

       % cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/

2. Ensure the database is mounted. For example, if the database is shut down, run:

       STARTUP MOUNT

3. Recover the desired datafiles by specifying the `AUTOMATIC` keyword. This example performs automatic recovery on the whole database:

       RECOVER AUTOMATIC DATABASE

   The database automatically suggests and applies the necessary archived logs.

4. Open the database. For example:

       ALTER DATABASE OPEN;

If you use an Oracle Real Application Clusters configuration, and if you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first redo thread. You may have to manually apply the first log file from the other redo threads. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent logs in this thread.

See Also: Your platform-specific Oracle documentation for examples of log file application

## Recovering When Archived Logs Are in the Default Location

Recovering when the archived logs are in their default location is the simplest case. As a log is needed, the database suggests the filename. If you are running nonautomatic media recovery with SQL*Plus, then the output is displayed in this format:

    ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
    ORA-00289: Suggestion : logfile
    ORA-00280: Change #### for thread # is in sequence #
    Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

For example, SQL*Plus displays output similar to the following:

    ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1
    ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc
    ORA-00280: change 53577 for thread 1 is in sequence #802
    Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]

Similar messages are returned when you use an `ALTER` `DATABASE` `...` `RECOVER` statement. However, no prompt is displayed.

The database constructs suggested archived log filenames by concatenating the current values of the initialization parameters `LOG_ARCHIVE_DEST_``n` (where `n` is the highest value among all enabled, local destinations) and `LOG_ARCHIVE_FORMAT` and using log history data from the control file. The following are possible settings:

    LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/'
    LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

    SELECT NAME FROM V$ARCHIVED_LOG;

    NAME
    ----------------------------------------
    /oracle/oradata/trgt/arch/arcr_1_467.arc
    /oracle/oradata/trgt/arch/arcr_1_468.arc
    /oracle/oradata/trgt/arch/arcr_1_469.arc

Thus, if all the required archived log files are mounted at the `LOG_ARCHIVE_DEST_1` destination, and if the value for `LOG_ARCHIVE_FORMAT` is never altered, then the database can suggest and apply log files to complete media recovery automatically.

## Recovering When Archived Logs Are in a Nondefault Location

Performing media recovery when archived logs are not in their default location adds an extra step. You have the following mutually exclusive options:

- Edit the `LOG_ARCHIVE_DEST_``n` parameter that specifies the location of the archived redo logs, then recover as usual.

- Use the `SET` statement in SQL*Plus to specify the nondefault log location before recovery, or the `LOGFILE` parameter of the `RECOVER` command

## Resetting the Archived Log Destination

You can edit the initialization parameter file or issue `ALTER` `SYSTEM` statements to change the default location of the archived redo logs.

To change the default archived log location before recovery:

1. Use an operating system utility to restore the archived logs to a nondefault location. For example, enter:

       % cp /backup/arch/* /tmp/

2. Change the value for the archive log parameter to the nondefault location. You can issue `ALTER` `SYSTEM` statements while the instance is started, or edit the initialization parameter file and then start the database instance. For example, while the instance is shut down edit the parameter file as follows:

       LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'
       LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc

3. Using SQL*Plus, start a new instance by specifying the edited initialization parameter file, and then mount the database. For example, enter:

       STARTUP MOUNT

4. Begin media recovery as usual. For example, enter:

       RECOVER DATABASE

## Overriding the Archived Log Destination

In some cases, you may want to override the current setting for the archiving destination parameter as a source for redo log files.

To recover archived logs in a nondefault location with SET LOGSOURCE:

1. Using an operating system utility, copy the archived redo logs to an alternative location. For example, enter:

       % cp $ORACLE_HOME/oradata/trgt/arch/* /tmp

2. Specify the alternative location within SQL*Plus for the recovery operation. Use the `LOGSOURCE` parameter of the `SET` statement or the `RECOVER` `...` `FROM` clause of the `ALTER` `DATABASE` statement. For example, start SQL*Plus and run:

       SET LOGSOURCE "/tmp"

3. Recover the offline tablespace. For example, to recover the offline tablespace `users` do the following:

       RECOVER AUTOMATIC TABLESPACE users

4. Alternatively, you can avoid running `SET` `LOGSOURCE` and simply run:

       RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"

Note: Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.

## Responding to Unsuccessful Application of Redo Logs

If you are using SQL*Plus's recovery options (not SQL statements), then each time the database successfully applies a redo log file, the following message is returned:

    Log applied.

You are then prompted for the next log in the sequence or, if the most recently applied log is the last required log, terminates recovery.

If the suggested file is incorrect or you provide an incorrect filename, then the database returns an error message. For example, you may see something like:

    ORA-00308: cannot open archived log "/oracle/oradata/trgt/arch/arcr_1_811.arc"
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3

Recovery cannot continue until the required redo log is applied. If the database returns an error message after supplying a log filename, then the following responses are possible.

Error | Possible Cause | Solution
--- | --- | ---
`ORA-27037`: `unable` `to` `obtain` `file` `status` | Entered wrong filename. Log is missing. | Reenter correct filename. Restore backup archived redo log.
`ORA-27047:` `unable` `to` `read` `the` `header` `block` `of` `file` | The log may have been partially written or become corrupted. | If you can locate an uncorrupted or complete log copy, then apply the intact copy and continue recovery. If no copy of the log exists and you know the time of the last valid redo entry, then you use incomplete recovery. Restore backups and restart recovery.

## Interrupting User-Managed Media Recovery

If you start media recovery and must then interrupt it, for example, because a recovery operation must end for the night and resume the next morning, then take either of the following actions:

- Enter the word `CANCEL` when prompted for a redo log file.

- Use your operating system's interrupt signal if you must terminate when recovering an individual datafile, or when automated recovery is in progress.

After recovery is canceled, you can resume it later with the `RECOVER` command. Recovery resumes where it left off when it was canceled.